#include "Database.h"
#include "GlobalParam.h"
#include "Typedef.h"
#include "BookInfoList.h"
#include "History.h"

#include <fstream>

#define SQLITE_SQL_STATEMENT_LENGTH 500

DBSqlite::DBSqlite(Bpchar DatabasePath, Bpchar InitScriptPath)
{
    DatabaseFilePath = DatabasePath;
    DatabaseInitScriptPath = InitScriptPath;

    Handle = NULL;

    OpenDatabase();
}

DBSqlite::~DBSqlite()
{
    if(Handle)
        CloseDatabase();
}

BPcode DBSqlite::CheckTables()
{
    return BP_OK;
}

BPcode DBSqlite::DeleteTables()
{
    return BP_OK;
}

BPcode DBSqlite::InitTables()
{
    uint32_t ret;
    BPcode rv = BP_BAD;
    sqlite3_stmt* stmt = NULL;
    string tmp;
    string script;
    char* errmsg = NULL;

    fstream fin(DatabaseInitScriptPath.c_str());
    if(!fin)
    {
        bplog(LOG_LEVEL_DEBUG,"DBSqlite:Could not open initialization script,FilePath=%s",DatabaseInitScriptPath.c_str());
        return BP_BAD;
    }

    while(getline(fin,tmp))
        script += tmp;
    fin.close();

    bplog(LOG_LEVEL_DEBUG,"DBSqlite:script for initilization are:%s",script.c_str());

    if(!Handle)
        OpenDatabase();

/*
    ret = sqlite3_prepare_v2(Handle,script.c_str(),-1,&stmt,NULL);
    if(ret != SQLITE_OK)
    {
        bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for initializing tables is failed");
        rv = BP_BAD;
        goto end;
    }
    ret = sqlite3_step(stmt);
    if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
    {
        bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for initializing tables is failed");
        rv = BP_BAD;
        goto end;
    }
*/

    ret = sqlite3_exec(Handle,script.c_str(),0,0,&errmsg);
    if(ret != SQLITE_OK)
    {
        bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for initializing tables is failed, sqlite3 returns errmsg:%s",errmsg);
        rv = BP_BAD;
        goto end;
    }

    bplog(LOG_LEVEL_DEBUG,"DBSqlite:database's tables are successfully initialized");
    rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    if(errmsg)
        sqlite3_free(errmsg);

    return rv;
}

BPcode DBSqlite::OpenDatabase()
{
    uint32_t ret;

    if(Handle)
        CloseDatabase();

    ret = sqlite3_open_v2(DatabaseFilePath.c_str(),&Handle,
                            SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NULL);
    if(ret != SQLITE_OK)
    {
         bplog(LOG_LEVEL_ERROR,"DBSqlite:Fail to open database");
         return BP_BAD;
    }

    DatabaseStatus = DatabaseOpen;
    bplog(LOG_LEVEL_INFO,"DBSqlite:Database is successfully opened");

    return BP_OK;
}

BPcode DBSqlite::CloseDatabase()
{
    uint32_t ret;

	if(Handle)
	{
		ret = sqlite3_close(Handle);
		Handle = NULL;
	}

	DatabaseStatus = DatabaseClosed;

	return BP_OK;
}

BPcode DBSqlite::CheckDatabase()
{
    return BP_OK;
}

BPcode DBSqlite::AddBook(Book* book)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    BookInfo* pinfo = &(book->Info);
    uint32_t ret;
    BPcode rv = BP_BAD;
    Bbool BookExists;
    sqlite3_stmt* stmt = NULL;
    string Status;

    if(!Handle)
        OpenDatabase();

    bplog(LOG_LEVEL_DEBUG,"DBSqlite:Begin function AddBook,FilePath=%s",pinfo->BookFilePath.c_str());

    BookExists = CheckIfBookExistsByDigestHex(pinfo->BookDigest.digest_hex);
    if(BookExists)
    {
        SetBookAsExistedByDigestHex(pinfo->BookDigest.digest_hex);

        bplog(LOG_LEVEL_INFO,
              "DBSqlite:Book with digest hex:%s has already exists in database, set it as existed and cancel the action",
              pinfo->BookDigest.digest_hex.c_str());
        rv = BP_BAD;
        goto end;
    }

    Status = BookInfo::StatusToString(pinfo->BookStatus);

    sprintf(sql,"INSERT INTO book VALUES(NULL,'%s','%s','%s','%s',%d,'%s','%s',1);",
            pinfo->BookDigest.digest_hex.c_str(),
            Status.c_str(),pinfo->BookFilePath.c_str(),pinfo->BookName.c_str(),
            pinfo->PageCount,pinfo->Author.c_str(),pinfo->ISBN.c_str());

    bplog(LOG_LEVEL_DEBUG,"DBSqlite:SQL Statement is:%s",sql);

    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
    if(ret != SQLITE_OK)
    {
        bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for inserting book is failed");
        rv = BP_BAD;
        goto end;
    }
    ret = sqlite3_step(stmt);
    if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
    {
        bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for inserting book is failed");
        rv = BP_BAD;
        goto end;
    }

    bplog(LOG_LEVEL_DEBUG,"DBSqlite:add a book successfully");
    rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);

    return rv;
}

Book* DBSqlite::GetBookByID(uint32_t BookID)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret,rv;
    sqlite3_stmt* stmt = NULL;
    Book* book = new Book("");
    BookInfo* pinfo = &(book->Info);

    sprintf(sql,"SELECT * FROM book WHERE book_id = %d and existed=1;",BookID);

    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for selecting book is failed");
		bplog(LOG_LEVEL_ERROR,"DBSqlite:sqlite statement is %s",sql);
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for selecting book is failed");
		rv = BP_BAD;
		goto end;
	}

    pinfo->BookID = sqlite3_column_int(stmt,0);
    pinfo->BookDigest.digest_hex = (char *)sqlite3_column_text(stmt,1);
    pinfo->BookStatus = BookInfo::CharsToStatus((Bupchar)sqlite3_column_text(stmt,2));
    pinfo->BookFilePath = (char *)sqlite3_column_text(stmt,3);
    pinfo->BookName = (char *)sqlite3_column_text(stmt,4);
    pinfo->PageCount = sqlite3_column_int(stmt,5);
    pinfo->Author = (char *)sqlite3_column_text(stmt,6);
    pinfo->ISBN = (char *)sqlite3_column_text(stmt,7);

    rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);

    if(rv != BP_OK)
    {
        delete book;
        return NULL;
    }

    return book;
}

uint32_t DBSqlite::GetBookCount(string Keyword)
{
    uint32_t count = 0;
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret,rv;
    sqlite3_stmt* stmt = NULL;

    if(Keyword.length() == 0)
        sprintf(sql,"SELECT count(*) FROM book WHERE existed=1;");
    else
        sprintf(sql,"SELECT count(*) FROM book WHERE book_name like '%%%s%%' AND existed=1;",Keyword.c_str());

    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for getting book count is failed");
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for getting book count is failed");
		rv = BP_BAD;
		goto end;
	}

	count = sqlite3_column_int(stmt,0);

end:
    if(stmt)
        sqlite3_finalize(stmt);

    return count;
}

uint32_t DBSqlite::RefreshBookInfoList(uint32_t Page, BookInfo* InfoPtr, uint32_t Quantity, string Keyword)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    uint32_t rv;
    uint32_t count;
    sqlite3_stmt* stmt = NULL;
    uint32_t i;

    if(Quantity > BOOK_INFO_LIST_LENGTH)
    {
        rv = 0;
        bplog(LOG_LEVEL_INFO,"DBSqlite:maxinum of book list length is %d, your request for %d books is invalid, set book info list lenght as %u",
                        BOOK_INFO_LIST_LENGTH,Quantity,rv);
        goto end;
    }

    count = GetBookCount(Keyword);
    if(count < BOOK_INFO_LIST_LENGTH*(Page-1))
    {
        rv = 0;
        bplog(LOG_LEVEL_INFO,"DBSqlite:database has only %d books, your request for %d to %d books is invalid, set book info list lenght as %u",
                        count,(Page-1)*BOOK_INFO_LIST_LENGTH,Page*BOOK_INFO_LIST_LENGTH,rv);
        goto end;
    }

    if(Keyword.length() == 0)
        sprintf(sql,"SELECT * FROM book WHERE existed=1 LIMIT %d OFFSET %d",BOOK_INFO_LIST_LENGTH,BOOK_INFO_LIST_LENGTH*(Page-1));
    else
        sprintf(sql,"SELECT * FROM book WHERE book_name like '%%%s%%' AND existed=1 LIMIT %d OFFSET %d",Keyword.c_str(),BOOK_INFO_LIST_LENGTH,BOOK_INFO_LIST_LENGTH*(Page-1));
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
	    bplog(LOG_LEVEL_ERROR,"DBSqlite:sqlite statement is %s",sql);
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for getting %d page books is failed",Page);
		rv = BP_BAD;
		goto end;
	}
	i = 0;
	while(ret=sqlite3_step(stmt)==SQLITE_ROW || ret==SQLITE_DONE)
	{
	    if(i >= BOOK_INFO_LIST_LENGTH)
            break;

        InfoPtr[i].BookID = sqlite3_column_int(stmt,0);
        InfoPtr[i].BookDigest.digest_hex = (char *)sqlite3_column_text(stmt,1);
        InfoPtr[i].BookStatus = BookInfo::CharsToStatus((Bupchar)sqlite3_column_text(stmt,2));
        InfoPtr[i].BookFilePath = (char *)sqlite3_column_text(stmt,3);
        InfoPtr[i].BookName = (char *)sqlite3_column_text(stmt,4);
        InfoPtr[i].PageCount = sqlite3_column_int(stmt,5);
        InfoPtr[i].Author = (char *)sqlite3_column_text(stmt,6);
        InfoPtr[i].ISBN = (char *)sqlite3_column_text(stmt,7);

        i++;
	}

    bplog(LOG_LEVEL_INFO,"DBSqlite:successfully refresh book info list as page %d books, which now has %u books",Page,i);
    rv = i;

end:
    if(stmt)
        sqlite3_finalize(stmt);

    return rv;
}

Bbool DBSqlite::CheckIfBookExistsByDigestHex(string DigestHex)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    Bbool rv = false;
    sqlite3_stmt* stmt = NULL;
    int count;

    sprintf(sql,"SELECT count(*) FROM book WHERE digest_hex='%s'",DigestHex.c_str());
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for checking if book exists with digest hex is failed");
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for checking if book exists with digest hex is failed");
		rv = BP_BAD;
		goto end;
	}

	count = sqlite3_column_int(stmt,0);
	if(count == 0)
        rv = false;
    else
        rv = true;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

Bbool DBSqlite::SetBookAsExistedByDigestHex(string DigestHex)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    Bbool rv = false;
    sqlite3_stmt* stmt = NULL;
    int count;

    sprintf(sql,"UPDATE book SET existed=1 WHERE digest_hex='%s'",DigestHex.c_str());
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement that set book as existed is failed");
		rv = false;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement that set book as existed is failed");
		rv = false;
		goto end;
	}

    rv = true;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

string DBSqlite::GetSearchDirectory()
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    string rv = "";
    sqlite3_stmt* stmt = NULL;

    sprintf(sql,"SELECT book_path FROM bplayer_info WHERE info_id=1;");
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for getting search directory");
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for getting search directory");
		goto end;
	}

	rv = (char *)sqlite3_column_text(stmt,0);

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

BPcode DBSqlite::SetSearchDirectory(string directory)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    BPcode rv;
    BPcode ret2;
    sqlite3_stmt* stmt = NULL;
    string OldDirectory;

    OldDirectory = GetSearchDirectory();
    if(directory == OldDirectory)
    {
        bplog(LOG_LEVEL_INFO,"DBSqlite:new search directory is:%s, the same as the old",directory.c_str());
        return BP_DUPLICATE;
    }

    sprintf(sql,"UPDATE bplayer_info SET book_path='%s' WHERE info_id=1",directory.c_str());
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement for setting search directory");
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement for setting search directory");
		rv = BP_BAD;
		goto end;
	}

	ret2 = SetAllBooksNonExisted();
	if(ret2 != BP_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:failed to set all books as non existed");
		rv = BP_BAD;
		goto end;
	}

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

BPcode DBSqlite::SetAllBooksNonExisted()
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    BPcode rv;
    sqlite3_stmt* stmt = NULL;

    sprintf(sql,"UPDATE book SET existed=0;");
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement that set all books non existed");
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement that set all books non existed");
		rv = BP_BAD;
		goto end;
	}

	rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

BPcode DBSqlite::GetHistorysByBookID(uint32_t BookID, list<History*>* HList)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    BPcode rv;
    sqlite3_stmt* stmt = NULL;
    uint32_t i;

    sprintf(sql,"SELECT read_page,read_date,history_id FROM book_history WHERE book_id=%d;",BookID);
    bplog(LOG_LEVEL_INFO,"DBSqlite:sqlite statement that get read history is '%s'",sql);
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement that get read history");
		rv = BP_BAD;
		goto end;
	}
    i = 0;
	while(ret=sqlite3_step(stmt)==SQLITE_ROW || ret==SQLITE_DONE)
	{
	    History* ThisHistory = new History();

        ThisHistory->ReadPage = sqlite3_column_int(stmt,0);
        ThisHistory->ReadDate = (char *)sqlite3_column_text(stmt,1);
        ThisHistory->HistoryID = sqlite3_column_int(stmt,2);

        bplog(LOG_LEVEL_DEBUG,"DBSqlite:Record %d,ReadPage=%d,ReadDate=%s,HistoryID=%d",i,ThisHistory->ReadPage,ThisHistory->ReadDate.c_str(),ThisHistory->HistoryID);

        HList->push_back(ThisHistory);
        i++;
	}

    bplog(LOG_LEVEL_INFO,"DBSqlite:executing sqlite statement that get read history is success,get %d records",i);
	rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

BPcode DBSqlite::AddHistory(History* history,uint32_t BookID)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    BPcode rv;
    sqlite3_stmt* stmt = NULL;

    sprintf(sql,"INSERT INTO book_history VALUES(NULL,%d,%d,'%s');",BookID,history->ReadPage,history->ReadDate.c_str());
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement that insert read history");
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement that insert read history");
		rv = BP_BAD;
		goto end;
	}

    bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement that insert read history is success");
	rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}

BPcode DBSqlite::DeleteHistory(uint32_t HistoryID)
{
    Bchar sql[SQLITE_SQL_STATEMENT_LENGTH] = {0};
    uint32_t ret;
    BPcode rv;
    sqlite3_stmt* stmt = NULL;

    sprintf(sql,"DELETE FROM book_history WHERE history_id=%d;",HistoryID);
    ret = sqlite3_prepare_v2(Handle,sql,SQLITE_SQL_STATEMENT_LENGTH,&stmt,NULL);
	if(ret != SQLITE_OK)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:preparing sqlite statement that delete read history");
		rv = BP_BAD;
		goto end;
	}
	ret = sqlite3_step(stmt);
	if(ret!=SQLITE_DONE && ret!=SQLITE_ROW)
	{
		bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement that delete read history");
		rv = BP_BAD;
		goto end;
	}

    bplog(LOG_LEVEL_ERROR,"DBSqlite:executing sqlite statement that delete %d history is success",HistoryID);
	rv = BP_OK;

end:
    if(stmt)
        sqlite3_finalize(stmt);
    return rv;
}
